package es.aradesh.ministryassist.db;

import java.sql.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.provider.BaseColumns;

/**
 * Simple notes database access helper class. Defines the basic CRUD operations
 * for the notepad example, and gives the ability to list all notes as well as
 * retrieve or modify a specific note.
 * 
 * This has been improved from the first version of this tutorial through the
 * addition of better error handling and also using returning a Cursor instead
 * of using a collection of inner classes (which is less scalable and not
 * recommended).
 */
public class DbAdapter {

	public static final String TABLE_NOTES= "notes";
	public static final String TABLE_TERRITORY= "territory";
	public static final String TABLE_STREET= "street";
	//public static final String TABLE_BUILDING= "building";
	public static final String TABLE_HOME= "home";

	//public static final String[] NUM_KEYS = { KEY_NOTES_MODIFICATIONDATE, KEY_NOTES_CREATIONDATE};
	public static final String TAG = "DbAdapter";
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;


	public static final String DATABASE_NAME = "ministryassistant";
	//private static final int DATABASE_VERSION = 4;

	private final Context mCtx;

	/**
	 * Constructor - takes the context to allow the database to be
	 * opened/created
	 * 
	 * @param ctx
	 *            the Context within which to work
	 */
	public DbAdapter(Context ctx) {
		this.mCtx = ctx;
	}

	/**
	 * Open the notes database. If it cannot be opened, try to create a new
	 * instance of the database. If it cannot be created, throw an exception to
	 * signal the failure
	 * 
	 * @return this (self reference, allowing this to be chained in an
	 *         initialization call)
	 * @throws SQLException
	 *             if the database could be neither opened or created
	 */
	public DbAdapter open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	/**
	 * Create a new territory using the parameters provided. If it's
	 * successfully created return the new rowId for that territory, otherwise return
	 * a -1 to indicate failure.
	 * 
	 * @param name
	 * @param number
	 * @param town
	 * @return
	 */
	public long insertTerritory(int number, String town){
		ContentValues val = new ContentValues();
		val.put(TerritoryColumns.NUMBER, number);
		val.put(TerritoryColumns.TOWN, town);
		return mDb.insert(TABLE_TERRITORY, null, val);
	}
	public long insertStreet(long territory_id, String name, int type){
		ContentValues val = new ContentValues();
		val.put(StreetColumns.TERRITORY_ID, territory_id);
		val.put(StreetColumns.NAME, name);
		val.put(StreetColumns.TYPE, type);
		return mDb.insert(TABLE_STREET, null, val);
	}	
	
	/**
	 * Create a new home using the parameters provided. If it's
	 * successfully created return the new rowId for that home, otherwise return
	 * a -1 to indicate failure.
	 * 
	 * @param name
	 * @param streetId
	 * @param buildingId
	 * @param levels
	 * @param doors 
	 * @param status
	 * @param owner
	 * @return
	 */
	public long insertHome(String name, long streetId, long buildingId, int levels, int doors, int status, String owner){
		ContentValues val = new ContentValues();
		val.put(HomeColumns.NAME, name);
		val.put(HomeColumns.STREET_ID, streetId);
		val.put(HomeColumns.BUILDING_ID, buildingId);
		val.put(HomeColumns.LEVELS, levels);
		val.put(HomeColumns.DOORS, doors);
		val.put(HomeColumns.STATUS, status);
		val.put(HomeColumns.OWNER, owner);
		return mDb.insert(TABLE_HOME, null, val);
	}
	
	public long insertNote(long homeId, String description, int type){
		ContentValues val = new ContentValues();
		Date now = new Date(System.currentTimeMillis());
		
		val.put(NoteColumns.HOME_ID, homeId);
		val.put(NoteColumns.TYPE, type);
		val.put(NoteColumns.DESCRIPTION, description);
		val.put(NoteColumns.CREATIONDATE, now.toString());
		return mDb.insert(TABLE_NOTES, null, val);
	}
	public ContentValues selectById(String table, long id){
		ContentValues result = new ContentValues();
		Cursor cursor = mDb.query(table, null, BaseColumns._ID+" = "+String.valueOf(id), null, null, null, null);
		cursor.moveToFirst();
		String[] cols = cursor.getColumnNames();
		for(int i=0;i<cols.length;i++){
			//if (NUM_KEYS[0].equals(cols[i]) || NUM_KEYS[1].equals(cols[i])) result.put(cols[i],cursor.getLong(i));
			//else
				result.put(cols[i],cursor.getString(i));
		}
		return result;
	}

	public ContentValues selectByName(String table, String column, String name){
		ContentValues result = new ContentValues();
		Cursor cursor = mDb.query(table, null, column+" = "+name, null, null, null, null);
		cursor.moveToFirst();
		String[] cols = cursor.getColumnNames();
		for(int i=0;i<cols.length;i++) result.put(cols[i],cursor.getString(i));
		return result;
	}
	public Cursor selectTable(String table, String selection, String orderBy){
		Cursor result = null;
		result=mDb.query(table, null, selection, null, null, null, orderBy);
		return result;
	}

	public Cursor selectTable(String table, String[] columns, String selection, String orderBy){
		Cursor result = null;
		result=mDb.query(table, columns, selection, null, null, null, orderBy);
		return result;
	}
	public boolean updateById(String table, long id, ContentValues newvalues){
		mDb.update(table, newvalues, BaseColumns._ID+" = "+String.valueOf(id), null);
		return true;
	}
	public boolean removeById(String table, long id){
		boolean result = true;
		int rows = mDb.delete(table, BaseColumns._ID + " = " + String.valueOf(id), null);
		if (rows != 1) result = false;
		return result;
	}
	/**
	 * Create a new note using the title and body provided. If the note is
	 * successfully created return the new rowId for that note, otherwise return
	 * a -1 to indicate failure.
	 * 
	 * @param title
	 *            the title of the note
	 * @param body
	 *            the body of the note
	 * @return rowId or -1 if failed
	 */
/*	public long createNote(String title, String body) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_TITLE, title);
		initialValues.put(KEY_BODY, body);

		return mDb.insert(DATABASE_TABLE, null, initialValues);
	}*/

	/**
	 * Delete the note with the given rowId
	 * 
	 * @param rowId
	 *            id of note to delete
	 * @return true if deleted, false otherwise
	 */
/*	public boolean deleteNote(long rowId) {

		return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
	}*/

	/**
	 * Return a Cursor over the list of all notes in the database
	 * 
	 * @return Cursor over all notes
	 */
	/*public Cursor fetchAllNotes() {

		return mDb.query(DATABASE_TABLE, new String[] { KEY_ROWID, KEY_TITLE,
				KEY_BODY }, null, null, null, null, null);
	}*/

	/**
	 * Return a Cursor positioned at the note that matches the given rowId
	 * 
	 * @param rowId
	 *            id of note to retrieve
	 * @return Cursor positioned to matching note, if found
	 * @throws SQLException
	 *             if note could not be found/retrieved
	 */
/*	public Cursor fetchNote(long rowId) throws SQLException {

		Cursor mCursor =

		mDb.query(true, DATABASE_TABLE, new String[] { KEY_ROWID, KEY_TITLE,
				KEY_BODY }, KEY_ROWID + "=" + rowId, null, null, null, null,
				null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;

	}*/

	/**
	 * Update the note using the details provided. The note to be updated is
	 * specified using the rowId, and it is altered to use the title and body
	 * values passed in
	 * 
	 * @param rowId
	 *            id of note to update
	 * @param title
	 *            value to set note title to
	 * @param body
	 *            value to set note body to
	 * @return true if the note was successfully updated, false otherwise
	 */
/*	public boolean updateNote(long rowId, String title, String body) {
		ContentValues args = new ContentValues();
		args.put(KEY_TITLE, title);
		args.put(KEY_BODY, body);

		return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
	}*/
}

